package javacode.primary_class;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * Lop Excel de tao 1 file excel chua noi dung convert tu file XML Lop co 2
 * thuoc tinh public WB = workbook cua file excel, WS = worksheet cua file excel
 */
public class Excel {

    public HSSFWorkbook WB;                                                     // workbook cua file Excel
    public HSSFSheet WS;                                                        // worksheet cua file Excel
    /**
     * tao mang chua ten cac cot trong header rot gon
     */
    public final static String ARRAY_HEADER_S[] = {"Comman", "Description", "Parameter", "Long Description",
        "Range", "Default Value", "Retrieval Mechanism"};
    /**
     * tao mang chua ten cac cot trong header day du
     */
    public final static String ARRAY_HEADER_F[] = {"Category", "Security", "Related Commands", "Command", "Description", "Input Format",
        "Parameters", "Long Description", "Range", "Default Value", "Retrieval Mechanism"};
    /**
     * tao mang chua cac mau cho phep chon cho chu, thu tu mau [trang, den,
     * xanh, do, vang]
     */
    public final static Short ARRAY_COLOR[] = {HSSFColor.WHITE.index, HSSFColor.BLACK.index, HSSFColor.BLUE.index, HSSFColor.GREEN.index,
        HSSFColor.RED.index, HSSFColor.ORANGE.index, HSSFColor.YELLOW.index, HSSFColor.PINK.index};
    /**
     * tao mang chua gia tri chieu ngang cac cot (7 cot)
     */
    public final static int ARRAY_COLUM_WITH_S[] = {5000, 12000, 4000, 15000, 7000, 4200, 6000};
    /**
     * tao mang chua gia tri chieu ngang cac cot day du (12 cot)
     */
    public final static int ARRAY_COLUM_WITH_F[] = {4000, 4000, 6000, 5000, 20000, 8000, 5000, 20000, 4000, 4000, 7000};

    /**
     * Ham khoi tao khong tham so tao file Excel co cau truc 7 cot
     */
    public Excel() {
        Excel ex = new Excel(0);                                                // goi lai ham khoi tao co tham so
    }

    /**
     * ham khoi tao, tao 1 doi tuong excel co tham so structure structure =
     * (0 cho 7 cot; 1 cho 11 cot)
     */
    public Excel(int stucture) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet();
        HSSFRow row0 = worksheet.createRow(0);                                  // tao dong dau tien chua header file excel
        if (stucture == 0) {
            HSSFCell cell[] = new HSSFCell[ARRAY_HEADER_S.length];
            for (int i = 0; i < ARRAY_HEADER_S.length; i++) {
                cell[i] = row0.createCell(i);
                cell[i].setCellValue(ARRAY_HEADER_S[i]);
            }
        } else if (stucture == 1) {
            HSSFCell cell[] = new HSSFCell[ARRAY_HEADER_F.length];
            for (int i = 0; i < ARRAY_HEADER_F.length; i++) {
                cell[i] = row0.createCell(i);
                cell[i].setCellValue(ARRAY_HEADER_F[i]);
            }
        }
        this.WB = workbook;
        this.WS = worksheet;
    }

    /**
     * ham dinh dang lai trang worksheet nhu tuy chon cua nguoi dung
     */
    public void formatExcel(int bg_header_color, int tx_header_color,
            int bg_body_color, int tx_body_color) {

        HSSFFont font_hd = this.WB.createFont();                                // tao font cho header
        font_hd.setFontHeightInPoints((short) 10);
        font_hd.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font_hd.setColor(ARRAY_COLOR[tx_header_color]);                     // set mau chu header

        HSSFCellStyle style_hd = this.WB.createCellStyle();
        style_hd.setFillForegroundColor(ARRAY_COLOR[bg_header_color]);      // set mau nen header
        style_hd.setFont(font_hd);
        style_hd.setFillPattern(HSSFCellStyle.VERTICAL_CENTER);             // chon kieu to mau nen                                    // thuoc tinh cho noi dung xuong hang khi khong du cho
        style_hd.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_hd.setBorderLeft((short) 1);

        HSSFFont font_bd = this.WB.createFont();                                // tao font cho body
        font_bd.setFontHeightInPoints((short) 10);
        font_bd.setColor(ARRAY_COLOR[tx_body_color]);                       // set mau chu body

        HSSFCellStyle style_bd = this.WB.createCellStyle();                     // tao style cho body
        style_bd.setFillForegroundColor(ARRAY_COLOR[bg_body_color]);        // set mau nen body
        style_bd.setFont(font_bd);
        style_bd.setFillPattern(HSSFCellStyle.VERTICAL_CENTER);             // chon kieu to mau nen
        style_bd.setWrapText(true);                                         // thuoc tinh cho noi dung xuong hang khi khong du cho
        style_bd.setAlignment(HSSFCellStyle.ALIGN_LEFT);                    // can le ngang
        style_bd.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);          // can le doc
        style_bd.setBorderBottom((short) 1);                                // tao duong vien        
        style_bd.setBorderTop((short) 1);
        style_bd.setBorderLeft((short) 1);
        style_bd.setBorderRight((short) 1);

        int num_row = this.WS.getPhysicalNumberOfRows();                        // so dong cua sheet
        int num_col = this.WS.getRow(0).getPhysicalNumberOfCells();             // so cot cua sheet

        // vong lap set dinh dang header
        for (int colum = 0; colum < num_col; colum++) {
            this.WS.getRow(0).getCell(colum).setCellStyle(style_hd);
            if (num_col > 7) {
                this.WS.setColumnWidth(colum, ARRAY_COLUM_WITH_F[colum]);
            } else {
                this.WS.setColumnWidth(colum, ARRAY_COLUM_WITH_S[colum]);
            }
        }
        // vong lap set dinh dang cho body  
        for (int row = 1; row < num_row; row++) {
            HSSFRow row_tmp = this.WS.getRow(row);                              // lay dong theo chi so vong lap
            Iterator iterator_cell = row_tmp.iterator();                        // tu dong tao bien lap qua cac cell
            while (iterator_cell.hasNext()) {                                   // lap trong khi con cell tren dong
                HSSFCell cell_tmp = (HSSFCell) iterator_cell.next();            // lay cell
                cell_tmp.setCellStyle(style_bd);                                // set style cho cell
            }
        }
    }

    /**
     * ham xuat ra file excel
     */
    public void outputFile(String name, String url) {
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(url + name);
            this.WB.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (IOException ex) {
            JOptionPane.showMessageDialog(null, "Có lỗi trong quá trình xuất file");
            Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}